<?php
include('lib/adodb-time.inc.php');
require_once('lib/ftree_utils.php');
require_once "HTML/Template/IT.php";

function GenColor($gen, $max)
{
	$step = (int) (256 / $max);
	$val = str_pad(dechex($step * $gen), 2, "0", STR_PAD_LEFT);
	return("FF".$val."00");
}

function AddSpouse($myID, $mySex, $lvl)
{
	global $descT;
	
	if ($mySex == "M") {
		$asSpouseFld = "husband";
		$mySpouseFld = "wife";
	} else {
		$asSpouseFld = "wife";
		$mySpouseFld = "husband";
	}
	$query = "SELECT $mySpouseFld FROM marriage WHERE `" .$asSpouseFld. "` = $myID";
	$marriage_result = mysql_query($query) or die("Could not retrieve marriage info: " . mysql_error());
	while ($aSpouse = mysql_fetch_array($marriage_result, MYSQL_ASSOC)) {
		if ($aSpouse[$mySpouseFld]>0) {
			$query = "INSERT INTO $descT (id, first, middle, last, suffix, nick, dob, sex, gen) SELECT id, first, middle, last, suffix, nick, dob, sex, $lvl FROM person WHERE `id` = " .$aSpouse[$mySpouseFld];
			$result = mysql_query($query) or die("Could not add spouse: " . mysql_error());
		}
	}
}

function FindDescs($myID, $mySex, $lvl)
{
	global $descT, $showSpouses, $limitDesc, $maxDesc;
	
	//$query = "INSERT INTO $descT SELECT id, first, middle, last, suffix, nick, dob, sex FROM person WHERE id = $myID";
	//$result = mysql_query($query) or die("Add person to temp table failed: " . mysql_error());

	$lvl++;
	if ($limitDesc && $lvl > $maxDesc) { return; }
	if ($mySex == "M") { $parent  = "father"; } else { $parent  = "mother"; }

	$query = "INSERT INTO $descT (id, first, middle, last, suffix, nick, dob, sex, gen) SELECT id, first, middle, last, suffix, nick, dob, sex, $lvl FROM person WHERE $parent = $myID";
	$result = mysql_query($query) or die("Add person's children to temp table failed: " . mysql_error());
	
	$query = "SELECT id, sex FROM person WHERE $parent = $myID";
	$result = mysql_query($query) or die("Could not select person's children: " . mysql_error());
	while ($aDesc = mysql_fetch_array($result, MYSQL_ASSOC)) {
		if ($showSpouses) { AddSpouse($aDesc['id'], $aDesc['sex'], $lvl); }
		FindDescs($aDesc['id'], $aDesc['sex'], $lvl);
	}
}

function DisplayFound()
{
	global $descT, $rpt, $sortOldestFirst, $showGen;
	
	$query = "SELECT MAX(gen) AS gen FROM $descT";
	$result = mysql_query($query) or die("Could not read temporary table: " . mysql_error());
	$maxGens = mysql_fetch_array($result, MYSQL_ASSOC);
	$maxGen = $maxGens['gen'];
	
	if ($sortOldestFirst) { $sortOrder="ASC"; } else { $sortOrder="DESC"; }
	$query = "SELECT * FROM $descT WHERE `dob` ORDER BY `dob` $sortOrder";
	$result = mysql_query($query) or die("Could not read temporary table: " . mysql_error());

	$rpt .= "<table>\n";
	$rpt .= "	<tr>\n";
	if ($showGen) {
	$rpt .= "		<th>Gen</th>\n";
	}
	$rpt .= "		<th>DOB</th>\n";
	$rpt .= "		<th>Name</th>\n";
	$rpt .= "	</tr>\n";
	
	while ($aDesc = mysql_fetch_array($result, MYSQL_ASSOC)) {
		$aDescFull = formatName($aDesc['first'], $aDesc['middle'], $aDesc['last'], $aDesc['suffix'], $aDesc['nick'], "fnmls");
		$aDescDOB = formatDate($aDesc['dob'],"year_first");
		$rpt .= "	<tr>\n";
		if ($showGen) {
		$rpt .= "		<td align=\"middle\" bgcolor=\"" . GenColor($aDesc['gen'],$maxGen) . "\">$aDesc[gen]</td>\n";
		}
		$rpt .= "		<td>$aDescDOB</td>\n";
		$rpt .= "		<td><a href=\"person.php?id=$aDesc[id]\">$aDescFull</a></td>\n";
		$rpt .= "	</tr>\n";
	}
	$rpt .= "</table>\n";
	
	$rpt .= "\n\n<h2>Unknown dates of birth:</h2>\n";
	
	$query = "SELECT * FROM $descT WHERE `dob` IS NULL ORDER BY `gen`, `last`, `first`, `middle`";
	$result = mysql_query($query) or die("Could not read temporary table: " . mysql_error());

	$rpt .= "<table>\n";
	$rpt .= "	<tr>\n";
	if ($showGen) {
	$rpt .= "		<th>Gen</th>\n";
	}
	$rpt .= "		<th>Name</th>\n";
	$rpt .= "	</tr>\n";

	while ($aDesc = mysql_fetch_array($result, MYSQL_ASSOC)) {
		$aDescFull = formatName($aDesc['first'], $aDesc['middle'], $aDesc['last'], $aDesc['suffix'], $aDesc['nick'], "fnmls");
		$aDescDOB = formatDate($aDesc['dob'],"year_first");
		$rpt .= "	<tr>\n";
		if ($showGen) {
		$rpt .= "		<td align=\"middle\" bgcolor=\"" . GenColor($aDesc['gen'],$maxGen) . "\">$aDesc[gen]</td>\n";
		}
		$rpt .= "		<td><a href=\"person.php?id=$aDesc[id]\">$aDescFull</a></td>\n";
		$rpt .= "	</tr>\n";
	}
	$rpt .= "</table>\n";
	

}



// MAIN

// GET HTTP "GET" VARS

if ( isset($_GET['id']) ) {
        $id = $_GET['id'];
} else {
        $id = 2186;
}
$id=preg_replace("/[^0-9]/","",$id);  //get rid of anything but numbers
if ($id < 1 || $id > pow(2,17)) {
        die("ID is out of range.");
}
if (isset($_POST['inclDOBsSpouses']) ) {
	$showSpouses = TRUE;
} else {
	$showSpouses = FALSE;
}
if ($_POST['sortDescDOBs'] == "oldest") {
	$sortOldestFirst = TRUE;
} else {
	$sortOldestFirst = FALSE;
}
if (isset($_POST['inclDOBsGens']) ) {
	$showGen = TRUE;
} else {
	$showGen = FALSE;
}
if ($_POST['maxDescDOBs'] == "All") {
	$limitDesc = FALSE;
	$maxDescLabel="all";
} else {
	$limitDesc = TRUE;
	$maxDesc = $_POST['maxDescDOBs'];
	$maxDescLabel=$maxDesc;
}

// SET UP TEMPLATE

$isMobile = ChkIsMobile();
$tpl = new HTML_Template_IT("templates");
$isMobile ? $template = "report_desc_DOBs_mtmpl.html" : $template = "report_desc_DOBs_tmpl.html";
$status = $tpl->loadTemplatefile($template, true, true);

/* Connecting, selecting database */
$link = mysql_connect("localhost", "ftreeweb", "ftree_web")
   or die("Could not connect : " . mysql_error());
mysql_select_db("ftree") or die("Could not select database");


// GET PERSON

$query = "SELECT * FROM person WHERE `id` = $id";
$result = mysql_query($query) or die("Query failed : " . mysql_error());
$idData = mysql_fetch_array($result, MYSQL_ASSOC);

$full = formatName($idData['first'], $idData['middle'], $idData['last'], $idData['suffix'], $idData['nick'], "fnmls");

$tpl->setVariable("PersonID", $id);
$tpl->setVariable("FullName", $full);

$rpt = "<h2>Descendents to $maxDescLabel generations, sorted by date of birth:</h2>\n";

// CREATE TEMPORARY TABLE using this person
/*
	We want to accumulate records in a temp table and then sort them all at once.
	We can give ftreeweb CREATE TEMPORARY TABLES rights on ftree, but it still needs INSERT rights,
	which we don't want to give (http://bugs.mysql.com/bug.php?id=27480, fixed in 5.0?)
	So we have already created a database "free_tmp" and given ftreeweb all the basic permissions on it.
	mysql> CREATE DATABASE ftree_tmp;
	Query OK, 1 row affected (0.00 sec)

	mysql> GRANT SELECT, INSERT, UPDATE, DELETE, DROP, ALTER, CREATE TEMPORARY TABLES ON ftree_tmp.* to ftreeweb@localhost;
	Query OK, 0 rows affected (0.00 sec)
*/

$descT = "ftree_tmp.desc_DOBs";

$query = "CREATE TEMPORARY TABLE $descT SELECT id, first, middle, last, suffix, nick, dob, sex FROM person WHERE id = $id;";
$result = mysql_query($query) or die ("Could not create temporary table: ". mysql_error());
$query = "ALTER TABLE $descT ADD COLUMN gen INTEGER";
$result = mysql_query($query) or die ("Could not add generation column: ". mysql_error());
$query = "UPDATE $descT SET gen = '0'";
$result = mysql_query($query) or die ("Could not set 0 generation: ". mysql_error());

if ($showSpouses) { AddSpouse($id, $idData['sex'], 0); }

FindDescs($id, $idData['sex'], 0);
DisplayFound();

$tpl->setVariable("ReportData", $rpt);

mysql_close($link);

$tpl->show();
?>
